package com.zglc.ms.shop.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.zglc.ms.shop.model.ShopProductLimit;

@Repository
public class ProductLimitDao {

	private Logger logger = LoggerFactory.getLogger(getClass());

	@Autowired
	private JdbcTemplate jdbcTemplate;

	public int insertShopProductLimit(ShopProductLimit shopProductLimit) {
		final String sql = "INSERT INTO shop_product_limit(shop_product_id, limit_type, user_level, limit_date_type, limit_num, "
				+ " status, created_at,  updated_at)" + " VALUES (?, ?, ?, ?, ?,  ?, NOW(), NOW())";
		System.out.println("插入产品sql： " + sql);
		System.out.println("插入参数： " + shopProductLimit.toString());
		KeyHolder keyHolder = new GeneratedKeyHolder();
		try {
			jdbcTemplate.update(new PreparedStatementCreator() {

				@Override
				public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
					PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
					ps.setString(1, shopProductLimit.getShopProductId());
					ps.setInt(2, shopProductLimit.getLimitType());
					ps.setLong(3, shopProductLimit.getUserLevel());
					ps.setLong(4, shopProductLimit.getLimitDateType());
					ps.setLong(5, shopProductLimit.getLimitNum());

					ps.setInt(6, shopProductLimit.getStatus());
					return ps;
				}
			}, keyHolder);
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println(e.toString());
			return -1;
		}
		return keyHolder.getKey().intValue();
	}

	public int UpdateShopProductLimit(ShopProductLimit shopProductLimit) {
		final String sql = "REPLACE INTO shop_product_limit(shop_product_id, limit_type, user_level, limit_date_type, limit_num, "
				+ " status, created_at,  updated_at, id)" + " VALUES (?, ?, ?, ?, ?,  ?, NOW(), NOW(), ?)";
		System.out.println("更新产品sql： " + sql);
		System.out.println("更新参数： " + shopProductLimit.toString());
		KeyHolder keyHolder = new GeneratedKeyHolder();
		try {
			jdbcTemplate.update(new PreparedStatementCreator() {

				@Override
				public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
					PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
					ps.setString(1, shopProductLimit.getShopProductId());
					ps.setInt(2, shopProductLimit.getLimitType());
					ps.setLong(3, shopProductLimit.getUserLevel());
					ps.setLong(4, shopProductLimit.getLimitDateType());
					ps.setLong(5, shopProductLimit.getLimitNum());

					ps.setInt(6, shopProductLimit.getStatus());
					ps.setInt(7, shopProductLimit.getId());
					return ps;
				}
			}, keyHolder);
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println(e.toString());
			return -1;
		}
		return keyHolder.getKey().intValue();
	}

	public ShopProductLimit getShopProductLimitById(int id) {
		final String sql = "SELECT * FROM shop_product_limit  WHERE id = ? AND status = true ";

		try {
			ShopProductLimit info = jdbcTemplate.queryForObject(sql, new Object[] { id },
					new RowMapper<ShopProductLimit>() {

						@Override
						public ShopProductLimit mapRow(ResultSet rs, int rowNum) throws SQLException {
							ShopProductLimit info = setShopProductLimit(rs);
							return info;
						}
					});

			return info;
		} catch (DataAccessException e) {
			logger.info("getShopProductLimitById e = " + e.getLocalizedMessage());
		}

		return null;
	}

	private ShopProductLimit setShopProductLimit(ResultSet rs) throws SQLException {
		ShopProductLimit info = new ShopProductLimit();
		info.setId(rs.getInt("id"));
		info.setShopProductId(rs.getString("shop_product_id"));
		info.setLimitType(rs.getInt("limit_type"));
		info.setUserLevel(rs.getInt("user_level"));
		info.setLimitDateType(rs.getInt("limit_date_type"));
		info.setLimitNum(rs.getInt("limit_num"));
		info.setStatus(rs.getInt("status"));
		info.setCreatedAt(rs.getDate("created_at"));
		info.setUpdatedAt(rs.getDate("updated_at"));
		return info;
	}

	public List<ShopProductLimit> getShopProductByAll() {

		final String sql = "SELECT * FROM shop_product_limit WHERE status = true ";
		try {
			List<ShopProductLimit> users = jdbcTemplate.query(sql, new Object[] {},
					new ResultSetExtractor<List<ShopProductLimit>>() {
						public List<ShopProductLimit> extractData(ResultSet rs)
								throws SQLException, DataAccessException {
							List<ShopProductLimit> list = new ArrayList<ShopProductLimit>();
							while (rs.next()) {
								ShopProductLimit info = setShopProductLimit(rs);
								list.add(info);
							}
							return list;
						}
					});
			return users;
		} catch (DataAccessException e) {
			logger.error("not exist ShopProductLimit by all = " + "|" + e.getLocalizedMessage());
			return null;
		}
	}

	public List<ShopProductLimit> getShopProductByProductId(int productId) {
		final String sql = "SELECT * FROM shop_product_limit where shop_product_id = ? AND status = true ORDER BY user_level";
		try {
			List<ShopProductLimit> users = jdbcTemplate.query(sql, new Object[] { productId },
					new ResultSetExtractor<List<ShopProductLimit>>() {
						public List<ShopProductLimit> extractData(ResultSet rs)
								throws SQLException, DataAccessException {
							List<ShopProductLimit> list = new ArrayList<ShopProductLimit>();
							while (rs.next()) {
								ShopProductLimit info = setShopProductLimit(rs);
								list.add(info);
							}
							return list;
						}
					});
			return users;
		} catch (DataAccessException e) {
			logger.error("not exist getShopProductByProductId  = " + "| " + productId + "|" + e.getLocalizedMessage());
			return null;
		}
	}
}
